package com.css.bjdt_core.executive.util;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.css.bjdt_core.cell.entity.Cell;
import com.css.bjdt_core.dataSetManage.entity.ViewDataItem;
import com.css.bjdt_core.executive.ReportExecuteException;
import com.css.bjdt_core.report.entity.ViewRptStatCategory;
import com.css.bjdt_core.report.entity.ViewRptTemplateCondition;
import com.css.bjdt_core.report.entity.RptTemplateCondition.ConditionType;
import com.css.bjdt_core.util.ReportConstant;
/**
 * 
 * 1.区域和条件是一对多的关系,条件和区域是一对一的关系
 * 2.区域和统计类别是一对一的关系,统计类别和区域是一对多的关系
 * 
 */
public class SqlExpProducer {

	private SqlExpProducer() {
	}
	
	private static class SingletonHolder {  
        private static final SqlExpProducer INSTANCE = new SqlExpProducer();  
    }

	public static final SqlExpProducer getInstance() {
		return SingletonHolder.INSTANCE;
	}
	
	private static Log logger = LogFactory.getLog(SqlExpProducer.class);

	//以条件类型为key
	private Map<String, List<ViewRptTemplateCondition>> conditionMap;
	//以信息集id+主外键标识符为key
	private Map<String, ViewDataItem> itemMap;

	/**
	 * 生成sql表达式
	 * @param cell
	 */
	public void produceSqlExp(Cell cell) {
		if (!verifyCondition(cell)) {
			return;
		}
		String baseClause = ReportConstant.BASE_SQL;
		baseClause = baseClause.replace(ReportConstant.MASTER_TABLE, cell.getStatCategory().getSetName());
		appendTotalCondition(cell);
		appendRowCondition(cell);
		appendColCondition(cell);
		appendCellCondition(cell);
		appendTimeCondition(cell);
		appendUnitCondition(cell);
		String sqlExp = baseClause + cell.getJoinClause() + ReportConstant.WHERE_KEY + cell.getWhereClause();
		cell.setSqlExp(sqlExp);
	}

	/**
	 * 验证单元格是否至少参与一种条件(除总控条件以外)
	 * @param cell
	 * @return
	 */
	private boolean verifyCondition(Cell cell) {
		if(cell.getStatCategory() == null){
			logger.error("cell id:"+cell.getCellId());
			throw new ReportExecuteException("cell must have a statCategory!");
		}
		List<ViewRptTemplateCondition> rowConditions = conditionMap.get(ConditionType.row.getKey());
		for (ViewRptTemplateCondition rowCondition : rowConditions) {
			if (cell.getRelRowNum() != null) {
				if (cell.getRelRowNum().toString().equals(rowCondition.getRelRowNum())) {
					return true;
				}
			}
		}
		List<ViewRptTemplateCondition> colConditions = conditionMap.get(ConditionType.col.getKey());
		for (ViewRptTemplateCondition colCondition : colConditions) {
			if (cell.getRelColNum() != null) {
				if (cell.getRelColNum().toString().equals(colCondition.getRelColNum())) {
					return true;
				}
			}
		}
		List<ViewRptTemplateCondition> cellConditions = conditionMap.get(ConditionType.cell.getKey());
		for (ViewRptTemplateCondition cellCondition : cellConditions) {
			if (cell.getAbsColNum().toString().equals(cellCondition.getAbsColNum()) && cell.getAbsRowNum().toString().equals(cellCondition.getAbsRowNum())) {
				return true;
			}
		}
		return false;
	}

	/**
	 * 添加条件通用方法
	 * @param cell
	 * @param conditions
	 */
	public void appendCondition(Cell cell, List<ViewRptTemplateCondition> conditions) {
		StringBuilder joinClause = new StringBuilder();
		StringBuilder whereClause = new StringBuilder();
		ViewRptStatCategory statCategory = cell.getStatCategory();
		for (ViewRptTemplateCondition condition : conditions) {
			//如果条件中的信息集不等于统计类别中的信息集,那么是多表联查,否则是单表查询
			if (!statCategory.getSetId().equals(condition.getSetId())) {
				//根据统计类别的信息集id获取统计类别所在表的外键
				ViewDataItem statCategoryItem = itemMap.get(statCategory.getSetId() + ReportConstant.FK);
				//根据条件的信息集id获取条件所在表的主键				
				ViewDataItem conditionItem = itemMap.get(condition.getSetId() + ReportConstant.PK);
				if (statCategoryItem == null || conditionItem == null) {
					logger.error("cell id:" + cell.getCellId()
							+ ",statCategory setId:" + statCategory.getSetId() 
							+ ",condition id:" + condition.getConditionId() 
							+ ",condition setId:" + condition.getSetId());
					throw new ReportExecuteException("statCategory's FK and condition's PK must not be null!");
				}
				String joinSql = ReportConstant.JOIN_SQL;
				joinSql = joinSql.replace(ReportConstant.MASTER_TABLE, statCategory.getSetName())
						.replace(ReportConstant.MASTER_TABLE_COLUMN, statCategoryItem.getItemName())
						.replace(ReportConstant.SUB_TABLE, condition.getSetName())
						.replace(ReportConstant.SUB_TABLE_COLUMN, conditionItem.getItemName());
				//多表联查时去掉重复join语句
				if (!joinClause.toString().contains(joinSql)) {
					joinClause.append(joinSql);
				}
			}
			//如果是1那么在字段前加（,如果是2那么在条件值后,关系运算符前加）
			String left = "1".equals(condition.getComboType()) ? "(" : "";
			String right = "2".equals(condition.getComboType()) ? ")" : "";

			String whereSql = ReportConstant.WHERE_SQL;
			whereSql = whereSql.replace(ReportConstant.LEFT, left)
					.replace(ReportConstant.MASTER_TABLE, condition.getSetName())
					.replace(ReportConstant.MASTER_TABLE_COLUMN, condition.getItemName())
					.replace(ReportConstant.RELA_OPERATOR, condition.getRelaOperatorName())
					.replace(ReportConstant.RIGHT, right)
					.replace(ReportConstant.LOGIC_OPERATOR, condition.getLogicOperatorName());
			if (condition.getRealConditionVal() != null) {
				whereSql = whereSql.replace(ReportConstant.CONDITION_VAL, condition.getRealConditionVal());
			}
			whereClause.append(whereSql);
		}
		//如果where子句与join子句均为空,那么直接赋值,否则拼加到从句最后面
		if (StringUtils.isBlank(cell.getWhereClause()) && StringUtils.isBlank(cell.getJoinClause())) {
			cell.setWhereClause(whereClause.toString());
			cell.setJoinClause(joinClause.toString());
		} else {
			cell.setWhereClause(cell.getWhereClause() + ReportConstant.AND_KEY + whereClause.toString());
			cell.setJoinClause(cell.getJoinClause() + joinClause.toString());
		}
	}

	/**
	 * 添加统计时间条件
	 * @param cell
	 */
	private void appendTimeCondition(Cell cell) {
		ViewRptStatCategory statCategory = cell.getStatCategory();
		if (StringUtils.isNotBlank(statCategory.getItemStatTimeName())) {
			ViewRptTemplateCondition startTimeCnd = new ViewRptTemplateCondition();
			startTimeCnd.setSetId(statCategory.getSetId());
			startTimeCnd.setSetName(statCategory.getSetName());
			startTimeCnd.setItemName(statCategory.getItemStatTimeName());
			startTimeCnd.setRelaOperator("2");//>=
			startTimeCnd.setConditionVal(ReportConstant.START_TIME_CONDITION_VAL);
			startTimeCnd.setValType(ReportConstant.PROTOTYPE);
			startTimeCnd.setLogicOperator("1");//and
			ViewRptTemplateCondition endTimeCnd = new ViewRptTemplateCondition();
			endTimeCnd.setSetId(statCategory.getSetId());
			endTimeCnd.setSetName(statCategory.getSetName());
			endTimeCnd.setItemName(statCategory.getItemStatTimeName());
			endTimeCnd.setRelaOperator("3");//<=
			endTimeCnd.setConditionVal(ReportConstant.END_TIME_CONDITION_VAL);
			endTimeCnd.setValType(ReportConstant.PROTOTYPE);
			List<ViewRptTemplateCondition> timeConditions = new ArrayList<ViewRptTemplateCondition>();
			timeConditions.add(startTimeCnd);
			timeConditions.add(endTimeCnd);
			appendCondition(cell, timeConditions);
		}
	}

	/**
	 * 添加人员所在党组织条件
	 * @param cell
	 */
	private void appendUnitCondition(Cell cell) {
		ViewRptTemplateCondition unitCnd = new ViewRptTemplateCondition();
		ViewRptStatCategory statCategory = cell.getStatCategory();
		unitCnd.setSetId(statCategory.getSetId());
		unitCnd.setSetName(statCategory.getSetName());
		unitCnd.setItemName(statCategory.getItemUnitName());
		unitCnd.setRelaOperator("10");//in
		unitCnd.setConditionVal(ReportConstant.UNIT_CONDITION_VAL);
		unitCnd.setValType(ReportConstant.PROTOTYPE);
		List<ViewRptTemplateCondition> unitConditions = new ArrayList<ViewRptTemplateCondition>();
		unitConditions.add(unitCnd);
		appendCondition(cell, unitConditions);
	}

	/**
	 * 添加总控条件
	 * @param cell
	 */
	private void appendTotalCondition(Cell cell) {
		List<ViewRptTemplateCondition> totalConditions = conditionMap.get(ConditionType.total.getKey());
		if (totalConditions != null)
			appendCondition(cell, totalConditions);
	}

	/**
	 * 添加行条件
	 * @param cell
	 */
	private void appendRowCondition(Cell cell) {
		List<ViewRptTemplateCondition> rowConditions = handleRowCondition(conditionMap.get(ConditionType.row.getKey()), cell);
		if (!rowConditions.isEmpty()) {
			appendCondition(cell, rowConditions);
		}
	}

	/**
	 * 添加列条件
	 * @param cell
	 */
	private void appendColCondition(Cell cell) {
		List<ViewRptTemplateCondition> colConditions = handleColCondition(conditionMap.get(ConditionType.col.getKey()), cell);
		if (!colConditions.isEmpty()) {
			appendCondition(cell, colConditions);
		}
	}

	/**
	 * 添加单元格条件
	 * @param cell
	 */
	private void appendCellCondition(Cell cell) {
		List<ViewRptTemplateCondition> cellConditions = handleCellCondition(conditionMap.get(ConditionType.cell.getKey()), cell);
		if (!cellConditions.isEmpty()) {
			appendCondition(cell, cellConditions);
		}
	}

	/**
	 * 处理行条件(取出所有匹配单元格行号的条件)
	 * @param conditions
	 * @param cell
	 * @return
	 */
	private List<ViewRptTemplateCondition> handleRowCondition(List<ViewRptTemplateCondition> conditions, Cell cell) {
		List<ViewRptTemplateCondition> list = new ArrayList<ViewRptTemplateCondition>();
		if (conditions != null) {
			for (ViewRptTemplateCondition condition : conditions) {
				if (cell.getRelRowNum() != null) {
					int[] colRange = getRange(condition, ConditionType.col);
					int[] rowRange = getRange(condition, ConditionType.row);
					if (cell.getRelRowNum().toString().equals(condition.getRelRowNum()) 
							&& ArrayUtils.contains(colRange, cell.getAbsColNum())
							&& ArrayUtils.contains(rowRange, cell.getAbsRowNum())) {
						list.add(condition);
					}
				}
			}
		}
		return list;
	}

	/**
	 * 处理列条件(取出所有匹配单元格列号的条件)
	 * @param conditions
	 * @param cell
	 * @return
	 */
	private List<ViewRptTemplateCondition> handleColCondition(List<ViewRptTemplateCondition> conditions, Cell cell) {
		List<ViewRptTemplateCondition> list = new ArrayList<ViewRptTemplateCondition>();
		if (conditions != null) {
			for (ViewRptTemplateCondition condition : conditions) {
				if (cell.getRelColNum() != null) {
					int[] rowRange = getRange(condition, ConditionType.row);
					int[] colRange = getRange(condition, ConditionType.col);
					if (cell.getRelColNum().toString().equals(condition.getRelColNum())
							&& ArrayUtils.contains(rowRange, cell.getAbsRowNum())
							&& ArrayUtils.contains(colRange, cell.getAbsColNum())) {
						list.add(condition);
					}
				}
			}
		}
		return list;
	}

	/**
	 * 处理单元格条件(取出所有匹配单元格行号和列号的条件,绝对坐标匹配)
	 * @param conditions
	 * @param cell
	 * @return
	 */
	private List<ViewRptTemplateCondition> handleCellCondition(List<ViewRptTemplateCondition> conditions, Cell cell) {
		List<ViewRptTemplateCondition> list = new ArrayList<ViewRptTemplateCondition>();
		if (conditions != null) {
			for (ViewRptTemplateCondition condition : conditions) {
				if (cell.getAbsColNum().toString().equals(condition.getAbsColNum()) && cell.getAbsRowNum().toString().equals(condition.getAbsRowNum())) {
					list.add(condition);
				}
			}
		}
		return list;
	}
	/**
	 * 根据区域和条件类型获取范围(行条件获取列范围;列条件获取行范围)
	 * @param condition
	 * @param type
	 * @return
	 */
	private int[] getRange(ViewRptTemplateCondition condition, ConditionType type) {
		if (condition.getAbsStartCol() == null 
				|| condition.getAbsEndCol() == null 
				|| condition.getAbsStartRow() == null 
				|| condition.getAbsEndRow() == null) {
			logger.error("condition id:"+condition.getConditionId());
			throw new ReportExecuteException("condition must be mapping to a area!");
		}
		if (ConditionType.col.equals(type)) {
			int startColNum = Integer.parseInt(condition.getAbsStartCol());
			int endColNum = Integer.parseInt(condition.getAbsEndCol());
			BigDecimal start = new BigDecimal(String.valueOf(startColNum));
			BigDecimal end = new BigDecimal(String.valueOf(endColNum));
			int[] range = new int[end.subtract(start).intValue() + 1];
			for (int j = startColNum, i = 0; j <= endColNum; j++, i++) {
				range[i] = j;
			}
			return range;
		} else if (ConditionType.row.equals(type)) {
			int startRowNum = Integer.parseInt(condition.getAbsStartRow());
			int endRowNum = Integer.parseInt(condition.getAbsEndRow());
			BigDecimal start = new BigDecimal(String.valueOf(startRowNum));
			BigDecimal end = new BigDecimal(String.valueOf(endRowNum));
			int[] range = new int[end.subtract(start).intValue() + 1];
			for (int i = startRowNum, j = 0; i <= endRowNum; i++, j++) {
				range[j] = i;
			}
			return range;
		} else {
			return new int[0];
		}
	}

	public void setConditionMap(Map<String, List<ViewRptTemplateCondition>> conditionMap) {
		this.conditionMap = conditionMap;
	}

	public void setItemMap(Map<String, ViewDataItem> itemMap) {
		this.itemMap = itemMap;
	}

	public void destory() {
		if (conditionMap != null) {
			conditionMap.clear();
			conditionMap = null;
		}
		if (itemMap != null) {
			itemMap.clear();
			itemMap = null;
		}
	}
	
}
